#!/bin/bash

APP=gmall

# 获取传递第2参数当做输入日期，如果没有输入参数，结束程序，提示传递参数华子
if [ -n "$2" ] ;then
   do_date=$2
else
   echo "请传入日期参数"
   exit
fi

#用户维度表
dim_user_info="
SET hive.stats.column.autogather = false ;
INSERT OVERWRITE TABLE ${APP}.dim_user_info PARTITION(dt = '9999-12-31')
SELECT
    id,
    login_name,
    nick_name,
    md5(name),
    md5(phone_num),
    md5(email),
    user_level,
    birthday,
    gender,
    create_time,
    operate_time,
    '${do_date}' AS start_date,
    '9999-12-31' AS end_date
FROM ${APP}.ods_user_info
WHERE dt = '${do_date}';
"

#sku商品维度表
dim_sku_info="
WITH sku AS (
    -- 1. 商品sku信息表
    SELECT
        id,
        price,
        sku_name,
        sku_desc,
        weight,
        is_sale,
        spu_id,
        category3_id,
        tm_id,
        create_time
    FROM ${APP}.ods_sku_info
    WHERE dt = '${do_date}'
), spu AS (
    -- 2. 商品spu信息表
    SELECT
        id,
        spu_name
    FROM ${APP}.ods_spu_info
    WHERE dt = '${do_date}'
), c3 AS (
    -- 3. 三级品类表
    SELECT
        id,
        name,
        category2_id
    FROM ${APP}.ods_base_category3
    WHERE dt = '${do_date}'
), c2 AS (
    -- 4. 二级品类表
    SELECT
        id,
        name,
        category1_id
    FROM ${APP}.ods_base_category2
    WHERE dt = '${do_date}'
), c1 AS (
    -- 5. 一级品类表
    SELECT
        id,
        name
    FROM ${APP}.ods_base_category1
    WHERE dt = '${do_date}'
), tm AS (
    -- 6. 品牌表
    SELECT
        id,
        tm_name
    FROM ${APP}.ods_base_trademark
    WHERE dt = '${do_date}'
), attr AS (
    -- 7. sku平台属性值
    SELECT
        sku_id,
        -- 将某个商品所有属性放到Set集合，并且去重
        collect_set(
            -- 将商品属性存入json字符串
            named_struct(
                'attr_id', attr_id, 'value_id', value_id, 'attr_name', attr_name, 'value_name', value_name
            )
        ) attrs
    FROM ${APP}.ods_sku_attr_value
    WHERE dt = '${do_date}'
    GROUP BY sku_id
), sale_attr AS (
    -- 8. sku销售属性值
    SELECT
        sku_id,
        collect_set(
            named_struct(
                'sale_attr_id', sale_attr_id, 'sale_attr_value_id', sale_attr_value_id,
                'sale_attr_name', sale_attr_name, 'sale_attr_value_name', sale_attr_value_name
            )
        ) sale_attrs
    FROM ${APP}.ods_sku_sale_attr_value
    WHERE dt = '${do_date}'
    GROUP BY sku_id
)
INSERT OVERWRITE TABLE ${APP}.dim_sku_info PARTITION(dt = '${do_date}')
SELECT
    sku.id,
    sku.price,
    sku.sku_name,
    sku.sku_desc,
    sku.weight,
    sku.is_sale,
    sku.spu_id,
    spu.spu_name,
    sku.category3_id,
    c3.name AS category3_name,
    c3.category2_id,
    c2.name AS category2_name,
    c2.category1_id,
    c1.name AS category1_name,
    sku.tm_id,
    tm.tm_name,
    attr.attrs AS sku_attr_values,
    sale_attr.sale_attrs AS sku_sale_attr_values,
    sku.create_time
FROM sku
LEFT JOIN spu ON sku.spu_id = spu.id
LEFT JOIN c3 ON sku.category3_id = c3.id
LEFT JOIN c2 ON c3.category2_id = c2.id
LEFT JOIN c1 ON c2.category1_id = c1.id
LEFT JOIN tm ON sku.tm_id = tm.id
LEFT JOIN attr ON sku.id = attr.sku_id
LEFT JOIN sale_attr ON sku.id = sale_attr.sku_id;
"

#地区维度表
dim_base_province="
INSERT OVERWRITE TABLE ${APP}.dim_base_province
SELECT
    bp.id,
    bp.name,
    bp.area_code,
    bp.iso_code,
    bp.iso_3166_2,
    bp.region_id,
    br.region_name
FROM ${APP}.ods_base_province bp
JOIN ${APP}.ods_base_region br ON bp.region_id = br.id;
"

#优惠卷维度表
dim_coupon_info="
SET hive.stats.column.autogather = false ;
INSERT OVERWRITE TABLE ${APP}.dim_coupon_info PARTITION(dt = '${do_date}')
SELECT
    id,
    coupon_name,
    coupon_type,
    condition_amount,
    condition_num,
    activity_id,
    benefit_amount,
    benefit_discount,
    create_time,
    range_type,
    limit_num,
    taken_count,
    start_time,
    end_time,
    operate_time,
    expire_time
FROM ${APP}.ods_coupon_info
WHERE dt = '${do_date}';
"

#活动维度表
dim_activity_rule_info="
WITH ar AS (
    -- 1. 活动规则表
    SELECT
        id,
        activity_id,
        activity_type,
        condition_amount,
        condition_num,
        benefit_amount,
        benefit_discount,
        benefit_level
    FROM ${APP}.ods_activity_rule
    WHERE dt = '${do_date}'
), ai AS (
    -- 2. 活动信息表
    SELECT
        id,
        activity_name,
        start_time,
        end_time,
        create_time
    FROM ${APP}.ods_activity_info
    WHERE dt = '${do_date}'
)
INSERT OVERWRITE TABLE ${APP}.dim_activity_rule_info PARTITION(dt = '${do_date}')
SELECT
    ar.id,
    ar.activity_id,
    ai.activity_name,
    ar.activity_type,
    ai.start_time,
    ai.end_time,
    ai.create_time,
    ar.condition_amount,
    ar.condition_num,
    ar.benefit_amount,
    ar.benefit_discount,
    ar.benefit_level
FROM ar
LEFT JOIN ai ON ar.activity_id = ai.id;
"

case $1 in
"dim_user_info"){
    hive -e "${dim_user_info}"
};;
"dim_sku_info"){
    hive -e "${dim_sku_info}"
};;
"dim_base_province"){
    hive -e "${dim_base_province}"
};;
"dim_coupon_info"){
    hive -e "${dim_coupon_info}"
};;
"dim_activity_rule_info"){
    hive -e "${dim_activity_rule_info}"
};;
"all"){
    hive -e "${dim_user_info}${dim_sku_info}${dim_coupon_info}${dim_activity_rule_info}${dim_base_province}"
};;
esac


#
#执行权限
# chmod u+x ods_to_dim_db_init.sh
#执行脚本
# ods_to_dim_db_init.sh all 2024-01-06
#



